/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

SET NOCOUNT ON
GO

USE master
GO
if exists (select * from sysdatabases where name='Northwind')
		drop database inhd
go

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

EXECUTE (N'CREATE DATABASE inhd
  ON PRIMARY (NAME = N''inhd'', FILENAME = N''' + @device_directory + N'inhd.mdf'')
  LOG ON (NAME = N''inhd_log'',  FILENAME = N''' + @device_directory + N'inhd.ldf'')')
go

exec sp_dboption 'inhd','trunc. log on chkpt.','true'
exec sp_dboption 'inhd','select into/bulkcopy','true'
GO

set quoted_identifier on
GO

/* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
   the default DATEFORMAT on the server.
*/
SET DATEFORMAT mdy
GO
use "inhd"
go

/* TaxMaster*/
CREATE TABLE TaxMaster ( 
[id] INTEGER IDENTITY(1,1) NOT NULL,
[taxUNK] VARCHAR(36) NOT NULL,
[taxDescription] NVARCHAR(255) NULL,
[percentage] REAL NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE TaxMaster
ADD CONSTRAINT [TaxMaster_PK] 
PRIMARY KEY ([taxUNK]); 
GO 

/* AccMaster */
CREATE TABLE AccMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[accUNK] VARCHAR(36) NOT NULL,
[accCode] BIGINT NOT NULL,
[accType] VARCHAR(255) NOT NULL,
[level] INTEGER NOT NULL,
[parentAcc] INTEGER NOT NULL,
[accDescription] NVARCHAR(255) NULL,
[TKCBalance] FLOAT NOT NULL,
[TKNBalance] FLOAT NOT NULL,
[lastAccess] DATETIME NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE AccMaster
ADD CONSTRAINT [AccMaster_PK] 
PRIMARY KEY ([accUNK]); 
GO 

/* CompanyInfo */
CREATE TABLE CompanyInfo ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[name] NVARCHAR(255) NOT NULL,
[address] NVARCHAR(1000) NOT NULL,
[phone] VARCHAR(255) NOT NULL,
[email] VARCHAR(255) NULL,
[director] VARCHAR(255) NOT NULL,
[taxCode] VARCHAR(255) NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 

/* TransactionMaster */
CREATE TABLE TransactionMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[transactionUNK] VARCHAR(36) NOT NULL,
[modified] DATETIME NOT NULL,
[taxUNK] VARCHAR(36) NULL,
[discountAmt] REAL NULL,
[totalSaleAmt] REAL NOT NULL,
[invTemplUNK] VARCHAR(36) NOT NULL,
[transType] INTEGER NOT NULL,
[paymentType] INTEGER NOT NULL,
[accUNK] VARCHAR(36) NOT NULL,
[cusUNK] VARCHAR(36) NOT NULL,
[staffUNK] VARCHAR(36) NOT NULL,
[status] INTEGER NOT NULL,
[customerFName] NVARCHAR(255) NOT NULL,
[customerLName] NVARCHAR(255) NOT NULL,
[taxName] NVARCHAR (255) NULL,
[taxPercentage] REAL NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE TransactionMaster
ADD CONSTRAINT [TransactionMaster_PK] 
PRIMARY KEY ([transactionUNK]); 
GO 

/* InvoiceTempl */
CREATE TABLE InvoiceTempl ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[invoiceUNK] VARCHAR(36) NOT NULL,
[content] VARBINARY(MAX) NULL,
[modifiedBy] INTEGER NOT NULL,
[modified] DATETIME NOT NULL,
[name] NVARCHAR(255) NOT NULL,
[code] VARCHAR(255) NULL,
[transType] varchar(10) NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE InvoiceTempl
ADD CONSTRAINT [InvoiceTempl_PK] 
PRIMARY KEY ([invoiceUNK]); 
GO 

/* RegMaster */
CREATE TABLE RegMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[regUNK] VARCHAR(36) NOT NULL,
[modified] DATETIME NOT NULL,
[modifiedBy] INTEGER NOT NULL,
[regDate] DATETIME NULL,
[invoiceUNK] VARCHAR(36) NOT NULL,
[status] VARCHAR(10) NOT NULL,
[receiverUNK] VARCHAR(36) NOT NULL,
[startDate] DATETIME NOT NULL,
[expiredDate] DATETIME NOT NULL,
[number] INTEGER NOT NULL CHECK ([number] > -1),
[maxUsingTimes] INTEGER NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE RegMaster
ADD CONSTRAINT [RegMaster_PK] 
PRIMARY KEY ([regUNK]); 
GO 

/* CustomerMaster */
CREATE TABLE CustomerMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[customerUNK] VARCHAR(36) NOT NULL,
[customerCode] VARCHAR(36) NOT NULL unique,
[firstName] NVARCHAR(255) NOT NULL,
[lastName] NVARCHAR(255) NOT NULL,
[address] NVARCHAR(1000) NULL,
[email] VARCHAR(255) NULL,
[phone] VARCHAR(10) NULL,
[fax] VARCHAR(20) NULL,
[totalBoughtAmt] REAL NULL,
[company] NVARCHAR(255) NULL,
[position] NVARCHAR(255) NULL,
[lastAccess] DATETIME NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE CustomerMaster
ADD CONSTRAINT [CustomerMaster_PK] 
PRIMARY KEY ([customerUNK]); 
GO 

/* PackageMaster */
CREATE TABLE PackageMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[packageUNK] VARCHAR(36) NOT NULL,
[quantity] BIGINT NOT NULL,
[salePrice] REAL NOT NULL,
[transactionUNK] VARCHAR(36) NOT NULL,
[productUNK] VARCHAR(36) NOT NULL,
[productCode] VARCHAR(10) NOT NULL,
[productName] NVARCHAR(255) NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE PackageMaster
ADD CONSTRAINT [PackageMaster_PK] 
PRIMARY KEY ([packageUNK]); 
GO 

/* SupplierMaster */
CREATE TABLE SupplierMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[supplierUNK] VARCHAR(36) NOT NULL,
[name] NVARCHAR(255) NOT NULL,
[contact] NVARCHAR(255) NULL,
[address] NVARCHAR(1000) NULL,
[email] VARCHAR(255) NULL,
[phone] VARCHAR(10) NULL,
[fax] VARCHAR(20) NULL,
[supplierCode] VARCHAR(255) NOT NULL unique,
[deliverDelay] INTEGER NOT NULL,
[freeDelivery] INTEGER NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE SupplierMaster
ADD CONSTRAINT [SupplierMaster_PK] 
PRIMARY KEY ([supplierUNK]); 
GO 

/* CategoryMaster */
CREATE TABLE CategoryMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[categoryUNK] VARCHAR(36) NOT NULL,
[name] NVARCHAR(255) NOT NULL,
[catDescrp] NVARCHAR(1000) NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE CategoryMaster
ADD CONSTRAINT [CategoryMaster_PK] 
PRIMARY KEY ([categoryUNK]); 
GO 

/* StaffMaster */
CREATE TABLE StaffMaster ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[staffUNK] VARCHAR(36) NOT NULL,
[staffCode] VARCHAR(255) NOT NULL unique,
[name] NVARCHAR(255) NULL,
[address] NVARCHAR(1000) NULL,
[phone] VARCHAR(255) NULL,
[email] VARCHAR(255) NULL,
[lastAccess] DATETIME NOT NULL,
[staffType] int NOT NULL,
[userName] VARCHAR(255) NOT NULL,
[passWord] VARCHAR(255) NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE StaffMaster
ADD CONSTRAINT [StaffMaster_PK] 
PRIMARY KEY ([staffUNK]); 
GO 

/* MngUnit */
CREATE TABLE MngUnit ( 
[id] INTEGER IDENTITY(1,1)NOT NULL,
[mngUNK] VARCHAR(36) NOT NULL,
[name] NVARCHAR(255) NOT NULL,
[address] NVARCHAR(1000) NOT NULL,
[phone] VARCHAR(10) NULL,
[fax] VARCHAR(20) NULL,
[email] VARCHAR(255) NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 
ALTER TABLE MngUnit
ADD CONSTRAINT [MngUnit_PK] 
PRIMARY KEY ([mngUNK]); 
GO 

/* ProductMaster */
CREATE TABLE ProductMaster ( 
[id] INTEGER IDENTITY(1,1) NOT NULL,
[productUNK] VARCHAR(36) NOT NULL,
[proDescription] NVARCHAR(1000) NULL,
[quantityOnStore] INTEGER NOT NULL CHECK ([quantityOnStore]>-1) ,
[retailUnitPrice] REAL NULL,
[wholesaleUnitPrice] REAL NULL,
[importPrice] REAL NULL,
[proName] NVARCHAR(255) NOT NULL,
[categoryUNK] VARCHAR(36) NOT NULL,
[categoryName] NVARCHAR(255),
[sn] VARCHAR(10) NOT NULL unique,
[supplierUNK] VARCHAR(36) NOT NULL,
[lastAccess] DATETIME NOT NULL,
[archiveFlag] VARCHAR(36) NULL
); 
GO 

ALTER TABLE ProductMaster
ADD CONSTRAINT [ProductMaster_PK] 
PRIMARY KEY ([productUNK]); 
GO 
ALTER TABLE TransactionMaster
ADD CONSTRAINT [StaffMaster_TransactionMaster_FK1] 
FOREIGN KEY ([staffUNK]) 
REFERENCES StaffMaster ([staffUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE RegMaster
ADD CONSTRAINT [MngUnit_RegMaster_FK1] 
FOREIGN KEY ([receiverUNK]) 
REFERENCES MngUnit ([mngUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE TransactionMaster
ADD CONSTRAINT [TaxMaster_TransactionMaster_FK1] 
FOREIGN KEY ([taxUNK]) 
REFERENCES TaxMaster ([taxUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE ProductMaster
ADD CONSTRAINT [SupplierMaster_ProductMaster_FK1] 
FOREIGN KEY ([supplierUNK]) 
REFERENCES SupplierMaster ([supplierUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE PackageMaster
ADD CONSTRAINT [ProductMaster_PackageMaster_FK1] 
FOREIGN KEY ([productUNK]) 
REFERENCES ProductMaster ([productUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE PackageMaster
ADD CONSTRAINT [TransactionMaster_PackageMaster_FK1] 
FOREIGN KEY ([transactionUNK]) 
REFERENCES TransactionMaster ([transactionUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE ProductMaster
ADD CONSTRAINT [CategoryMaster_ProductMaster_FK1] 
FOREIGN KEY ([categoryUNK]) 
REFERENCES CategoryMaster ([categoryUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE TransactionMaster
ADD CONSTRAINT [AccMaster_TransactionMaster_FK1] 
FOREIGN KEY ([accUNK]) 
REFERENCES AccMaster ([accUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE TransactionMaster
ADD CONSTRAINT [InvoiceTempl_TransactionMaster_FK1] 
FOREIGN KEY ([invTemplUNK]) 
REFERENCES InvoiceTempl ([invoiceUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 
ALTER TABLE RegMaster
ADD CONSTRAINT [InvoiceTempl_RegMaster_FK1] 
FOREIGN KEY ([invoiceUNK]) 
REFERENCES InvoiceTempl ([invoiceUNK]) ON UPDATE NO ACTION ON DELETE NO ACTION
GO 

CREATE LOGIN inhd WITH PASSWORD = '1' ,DEFAULT_DATABASE = inhd
GO    
EXEC master..sp_addsrvrolemember @loginame = N'inhd', @rolename = N'sysadmin'
GO
